package ru.vms.healthcontrol;

import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;


public class DBProvider {
	
	public static final String NOTUM_VALUES_TABLE = "notum_values";
	private static final String SENDED = "sended";
	private static final String DATE = "date";
	private static final String DAY = "day";
	private static final String VALUE = "value";
	private static final String NAME = "name";
	private static final String ID = "id";
	private static final String NOTUM_DB = "notumDB";
	private Context context;
	private DBService dbs;
	
	public DBProvider (Context context) {
		this.context = context;
		this.dbs = new DBService(this.context);
	}
	
	
	public void insert ( ValueUnderControl val ) {

		SQLiteDatabase db = dbs.getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put(NAME, val.getName());
		cv.put(VALUE, val.getValue());
		cv.put(DAY, val.getDateLongForDb());
		cv.put(DATE, val.getDateStrForDb());
		cv.put(SENDED, 0);
		
		long id = db.insertOrThrow(NOTUM_VALUES_TABLE, null, cv);
		NetLog.v("Insert in DB result : %d", id);
		
		db.close();
	}
	
	public int update ( ValueUnderControl val ) {
		
		SQLiteDatabase db = dbs.getWritableDatabase();
		
		ContentValues cv = new ContentValues();
		cv.put(NAME, val.getName());
		cv.put(VALUE, val.getValue());
		cv.put(DAY, val.getDateLongForDb());
		cv.put(DATE, val.getDateStrForDb());
		cv.put(SENDED, 0);
		
		int rows_updated = db.update(NOTUM_VALUES_TABLE, cv, DAY+" = ? AND "+NAME+" = ?", new String [] {val.getDateLongStrForDb(),val.getName()});
		
		NetLog.v("Update table %s were DAY = %s AND NAME = %s.  [ rows updated: %d ]",NOTUM_VALUES_TABLE, val.getDateLongStrForDb(), val.getName(), rows_updated);
		
		db.close();
		
		return rows_updated;
	}
	
	
	public int rowsByDate ( Date dt ) {
		
		int rows_count = 0;
		String date_long = new SimpleDateFormat("yyyyMMdd").format(dt);
		
		SQLiteDatabase db = dbs.getReadableDatabase();
		Cursor c = db.query(NOTUM_VALUES_TABLE, null, DAY+" = ?", new String[] {date_long}, null, null, null);
		
		if (c.moveToFirst()) {
	        NetLog.v("In table %s where DAY=%s is %d row(s)", NOTUM_VALUES_TABLE, date_long, c.getCount());
	        rows_count = c.getCount();
	      } else
	        NetLog.v("0 rows");
		
		c.close();
		db.close();
		
		return rows_count;
		 
	}
	
	
	public List<ValueUnderControl> selectByName ( String name ) {
		
		List<ValueUnderControl> values = null;
		
		SQLiteDatabase db = dbs.getReadableDatabase();
		
		Cursor c = db.query(NOTUM_VALUES_TABLE, null, NAME+" = ?", new String[] {name}, null, null, DAY+" desc");
		
		if (c.moveToFirst()) {

	        int idColIndex = c.getColumnIndex(ID);
	        int nameColIndex = c.getColumnIndex(NAME);
	        int valueColIndex = c.getColumnIndex(VALUE);
	        int dayColIndex = c.getColumnIndex(DAY);
	        int dateColIndex = c.getColumnIndex(DATE);
	        int sendedColIndex = c.getColumnIndex(SENDED);

	        values = new ArrayList<ValueUnderControl>(c.getCount());
	        
	        do {
	          
	        	String nm = c.getString(nameColIndex);
    			float val = c.getFloat(valueColIndex);
    			SimpleDateFormat format =new SimpleDateFormat("dd.MM.yyyy");
    			
    			Date dt;
				try {
					dt = format.parse(c.getString(dateColIndex));
				} catch (ParseException e) {
					e.printStackTrace();
					dt = new Date();
				}
    		
	        	
	        	NetLog.v(" %d %s %f   %d = %s  [%d] ",
	        			c.getInt(idColIndex),
	        			c.getString(nameColIndex),
	        			c.getFloat(valueColIndex),
	        			c.getLong(dayColIndex),
	        			c.getString(dateColIndex),
	        			c.getInt(sendedColIndex) );
	        	
	        	ValueUnderControl v = ValueUnderControl.create(ValueUnderControl.ValueType.WEIGHT, context);
	        	v.setFromDb( nm,val,dt );
	        	values.add(v);		

	        } while (c.moveToNext());
	        NetLog.v("In table %s is %d row(s)", NOTUM_VALUES_TABLE, c.getCount());
	        
	        
	      } else
	        NetLog.v("0 rows");
		c.close();
		
		db.close();
		
		return values;
	}
	
	
	public void selectAll ( ) {
		
		SQLiteDatabase db = dbs.getReadableDatabase();
		
		Cursor c = db.query(NOTUM_VALUES_TABLE, null, null, null, null, null, DAY+" desc");
		
		if (c.moveToFirst()) {

	        int idColIndex = c.getColumnIndex(ID);
	        int nameColIndex = c.getColumnIndex(NAME);
	        int valueColIndex = c.getColumnIndex(VALUE);
	        int dayColIndex = c.getColumnIndex(DAY);
	        int dateColIndex = c.getColumnIndex(DATE);
	        int sendedColIndex = c.getColumnIndex(SENDED);

	        do {
	          
	        	NetLog.v(" %d %s %f   %d = %s  [%d] ",
	        			c.getInt(idColIndex),
	        			c.getString(nameColIndex),
	        			c.getFloat(valueColIndex),
	        			c.getLong(dayColIndex),
	        			c.getString(dateColIndex),
	        			c.getInt(sendedColIndex) );
	        			

	        } while (c.moveToNext());
	        NetLog.v("In table %s is %d row(s)", NOTUM_VALUES_TABLE, c.getCount());
	        
	        
	      } else
	        NetLog.v("0 rows");
		c.close();
		
		db.close();
		
	}
	
	
	
	class DBService extends SQLiteOpenHelper {


		public DBService(Context context) {
			super(context, NOTUM_DB, null, 1);
		}

		@Override
		public void onCreate(SQLiteDatabase db) {
			NetLog.i("onCreate [DB v:%d]", db.getVersion());

			db.execSQL("create table "+NOTUM_VALUES_TABLE+" ("
					+ ID+" integer primary key autoincrement," 
					+ NAME +" text,"
					+ VALUE+" real,"
					+ DAY+" integer,"
					+ DATE+" text,"
					+ SENDED+" integer"
					+ ");");
		}

		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
			NetLog.i("onUpgrade [DB v:%d] old v:%d  new v:%d", db.getVersion(),oldVersion,newVersion);
		}
	}
}
